drop proc spa_SEC_GetManagedRoles
GO
/****** Object:  StoredProcedure [dbo].[spa_SEC_GetManagedRoles]    Script Date: 01/15/2009 10:29:33 ******/    
    
/*********************************************************************************************    
* PROCEDURE: [spa_SEC_GetManagedRoles]    
* PURPOSE: Get all Roles that can be assigned to other users    
* NOTES:    
* CREATED: Minh Nguyen 3/30/2011    
* MODIFIED:     
    
'-------------------------------------------------------------------------------------------------*    
' Date       | Version | Ticket #    |       Author             | Description    
' ========== | ======= | =========== | =========================| ====================================================================    
' 3/30/2011  | 1.0     | 16131       |  Minh Nguyen             |  CREATED NEW.
'-------------------------------------------------------------------------------------------------*    
exec [spa_SEC_GetManagedRoles] @UserRecId = '237DD22A-F833-4639-9A6D-1C5E0AD24107', @RoleId = 1

*/    
    
CREATE PROCEDURE [dbo].[spa_SEC_GetManagedRoles]    
	@UserRecId uniqueidentifier,
	@RoleId int,
	@EditedUserRecId uniqueidentifier = null
AS    
SET NOCOUNT ON

DECLARE @EditedUserRoleId int
SET @EditedUserRoleId = -1000

IF @EditedUserRecId IS NOT NULL
BEGIN
	SELECT @EditedUserRoleId = [RoleId] 
	FROM SEC_UsersInRole (nolock)
	WHERE UserRecId = @EditedUserRecId
END

IF exists(SELECT TOP 1 1 FROM SEC_AssignableRole (NOLOCK) WHERE [RoleId] = @RoleId AND AssignRoleId IS NULL)
BEGIN
	SELECT [RoleID], SystemId, RoleName, RoleCode,
			case when [RoleID] = @EditedUserRoleId then 1 else 0
			end as EditedUserRole
	FROM SEC_Role (NOLOCK) 
	WHERE [RoleID] <> @RoleId AND ActvStatus = '1'
	ORDER BY RoleName
END
ELSE
BEGIN
	SELECT h.[RoleId], h.SystemId, h.RoleName, h.RoleCode,
			case when h.[RoleId] = @EditedUserRoleId then 1 else 0
			end as EditedUserRole
	FROM SEC_AssignableRole ah (NOLOCK)
		JOIN SEC_Role h (NOLOCK) ON ah.AssignRoleId = h.[RoleID]
	WHERE ah.[RoleId] = @RoleId AND ActvStatus = '1'
	ORDER BY h.RoleName
END

SET NOCOUNT OFF